<?php
//创建一张学生表
create table student(
	id int(6) not null auto_increment comment '主键ID',
	name varchar(20) not null default '学并思' comment '姓名',
	sex enum('男','女') not null default '男' comment '性别',
	age int(3) not null default 18 comment '年龄',
	primary key(id),
	unique key u_name(name)
	)engine=innodb auto_increment=1 default charset=utf8;

//创建一个学生日记表
create table student_daylirecord(
	id int(6) auto_increment comment '日志表主键ID',
	content varchar(20) default '表内容为空' comment '表内容',
	times varchar(10) default '10' comment '查看次数' ,
	primary key(id)
	)engine=innodb auto_increment=1 default charset=utf8;

  insert into student(name,sex,age) value('mazhibin','男','19');
  insert into student(name,sex,age) value('chunhui','男','18');
  insert into student(name,sex,age) value('haipeng','男','1710');
  insert into student(name,sex,age) value('jiufeng','男','16');
  insert into student(name,sex,age) value('jianping','男','15');
  insert into student(name,sex,age) value('tianfu','男','14');
  insert into student(name,sex,age) value('tone','男','14');
  ALTER TABLE student ADD test int(6) comment "测试用";
  ALTER TABLE student CHANGE old_field_name new_field_name field_type

  select age ,age>17 as r_age from student;
  insert into test(name,sex,age) value('jiufeng','男','16');
  insert into test(name,sex,age) value('jianping','男','15');
  insert into test(name,sex,age) value('tianfu','男','14');
  insert into test(name,sex,age) value('tone','男','14');

  create table a(
  	  name varchar(30) character set utf8 collate utf8_bin,
  	  name2 varchar(30) character set utf8 collate utf8_general_ci
  	)
  insert into a(name,name2) values('a','a'),('b','b'),('A','A'),('B','B');
  alter table student rename srudent2;
  //添加id;
  //会报错已经存在主键（如果插入了数据），应该先把主键的字段删了。再重新插入主键ID 
 
  //以班级、性别作为分组，统计人数。
  select count(id),class,sex from stu group by class,sex;

?>